IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.CreateColumn'))
BEGIN
	DROP PROCEDURE MD3.CreateColumn
END
GO

CREATE Procedure MD3.CreateColumn   
( 
     @SchemaName		SYSNAME   
    ,@TableName	    	SYSNAME    
    ,@ColumnName		SYSNAME     
    ,@DataTypeText	    VARCHAR(100) = NULL --Data type
    ,@Length			VARCHAR(100) = NULL --Used for BINARY/CHAR/NCHAR/NVARCHAR/VARCHAR/VARBINARY
    ,@Precision		    SMALLINT = NULL     --Used for DECIMAL/NUMERIC e.g., for NUMERIC(19,2) use '19'
    ,@Scale		        SMALLINT = NULL     --Used for DECIMAL/NUMERIC e.g., for NUMERIC(19,2) use '2'
    ,@Nullable          BIT = 1
    ,@DropColumn		BIT = 0
    ,@IdentityColumn    BIT = 0   --this is ignored unless it is a NEW NOT NULL col
    ,@IdentitySeed      INT = 1
    ,@IdentityIncrement INT = 1
)
AS    

BEGIN
    SET NOCOUNT ON  

    DECLARE @LengthAsInt          SMALLINT
           ,@ColumnExists         BIT
           ,@IsIdentity           BIT
           ,@CurrentLength        SMALLINT
           ,@CurrentPrecision     SMALLINT
           ,@CurrentScale         SMALLINT
           ,@CurrentIs_nullable   BIT
           ,@LengthReductionFlag  BIT
           ,@RowCount             INT
           ,@Msg                  VARCHAR(MAX)
           ,@SQL                  VARCHAR(MAX)  
           ,@FormattedColumnData  VARCHAR(MAX)  


    ----------------------------------------------------------------------------------  
    -- Normalize Parameters  
    ----------------------------------------------------------------------------------  
	SELECT @SchemaName = MD3.f_RemoveBrackets (@SchemaName);
	SELECT @TableName = MD3.f_RemoveBrackets (@TableName);
	SELECT @ColumnName  = MD3.f_RemoveBrackets (@ColumnName);





    IF EXISTS (
			SELECT *
			FROM sys.objects o 
			JOIN sys.columns c
				ON o.object_id = c.object_id
			WHERE o.name = @TableName
			AND o.schema_id = SCHEMA_ID (@SchemaName)
			AND c.name = @ColumnName
	)
    BEGIN
        SET @ColumnExists = 1
    END
    ELSE
    BEGIN
        SET @ColumnExists = 0
    END;

	--if requested, we can DROP the column and any dependencies
    IF (@DropColumn = 1)
    BEGIN
        IF (@ColumnExists = 1)
        BEGIN
			PRINT 'DEVELOPER:  We are removing a column, please note any dependent items'
			PRINT 'that are also being dropped.  You will need to manually remove those'
			PRINT 'as well from the MD3 files.'

			--look for FKs that REFERENCE the col being removed, or where an FK is bound to the column
			--this needs work.  
			IF EXISTS (
				SELECT Const.*
				FROM sys.objects o
				CROSS APPLY MD3.f_GetForeignKeys (SCHEMA_NAME(o.schema_id),o.Name) Const
				WHERE Const.ParentKeyColumns LIKE '%' + @ColumnName + '%'
				OR Const.KeyColumns LIKE '%' + @ColumnName + '%'
			)
			BEGIN
				PRINT 'Stubbed.'
				PRINT 'We need to make a call to MD3.CreateForeignKey with the DropFK option here.'
				RAISERROR ('An error occurred, please see MD3.CreateColumn',16,1) 
			END;
            

            --remove any UNIQUE constraints
            EXEC MD3.RemoveUniqueConstraints
                 @SchemaName = @SchemaName
                ,@TableName = @TableName
                ,@KeyColumns = @ColumnName;

			--remove any PK dependencies. 
			--this won't handle compound keys though
            EXEC MD3.CreatePrimaryKey
                 @SchemaName = @SchemaName
                ,@TableName = @TableName
                ,@ColumnName = @ColumnName
				,@DropPK = 1;


        
            --remove indexes from column
			--here
		


            -- We can now drop the column.
            SET @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP COLUMN ' + @ColumnName;
            EXEC MD3.ExecuteSQL @SQL
            RETURN
        END
        ELSE
        BEGIN
            --column doesn't exist (anymore)
            RETURN
        END
    END

   	SELECT @FormattedColumnData = @DataTypeText

    -- The following data types are not supported:
    --    GEOGRAPHY
    --    GEOMETRY
    --    HIERARCHYID
    
    -- @LengthAsInt is used to make it easier to edit the @Length parameter. The alternative 
    -- is to CAST @Length to an integer data type every time it is used (to avoid inconsistencies
    -- when doing arithmetic operations on a string data type).
    IF (ISNUMERIC(@Length) = 1 AND @Length NOT LIKE '%.%') -- Verify it is an integer
    BEGIN
        SELECT @LengthAsInt = @Length
    END
    
    IF (@DataTypeText IN ('BINARY','CHAR'))
    BEGIN
        IF (@LengthAsInt IS NULL
         OR @LengthAsInt < 1
         OR @LengthAsInt > 8000
        )
        BEGIN
            SET @Msg = '@Length for this @DataTypeText must be > 0 and <= 8000.'
            RAISERROR(@Msg, 16, 1)
        END
        ELSE
        BEGIN
       	    SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
        END
    END

    ELSE 
    IF (@DataTypeText = 'NCHAR')
    BEGIN
        IF (@LengthAsInt IS NULL
         OR @LengthAsInt < 1
         OR @LengthAsInt > 4000
        )
        BEGIN
            SET @Msg = '@Length for this @DataTypeText must be > 0 and <= 4000.'
            RAISERROR(@Msg, 16, 1)
        END
        ELSE
        BEGIN
       	    SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
        END
    END

    ELSE 
    IF (@DataTypeText IN ('VARBINARY','VARCHAR'))
    BEGIN
        IF (@LengthAsInt IS NULL
         OR @LengthAsInt < 1
         OR @LengthAsInt > 8000
        ) 
            IF (@Length <> 'MAX'
             OR @Length IS NULL
            )
            BEGIN
                SET @Msg = '@Length for this @DataTypeText must be > 0 and <= 8000 or MAX.'
                RAISERROR(@Msg, 16, 1) 
            END
            ELSE
            BEGIN
        	    SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
            END
        ELSE
        BEGIN
         	SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
        END
    END

    ELSE 
    IF (@DataTypeText = 'NVARCHAR')
    BEGIN
        IF (@LengthAsInt IS NULL
         OR @LengthAsInt < 1
         OR @LengthAsInt > 4000
        ) 
            IF (@Length <> 'MAX'
             OR @Length IS NULL
            )
            BEGIN
                SET @Msg = '@Length for this @DataTypeText must be > 0 and <= 4000 or MAX.'
                RAISERROR(@Msg, 16, 1) 
            END
            ELSE
            BEGIN
        	    SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
            END
        ELSE
        BEGIN
         	SELECT @FormattedColumnData = @DataTypeText  + '(' + @Length + ')'
        END
    END

    ELSE 
    IF (@DataTypeText IN ('DECIMAL','NUMERIC'))
    BEGIN
        IF (@Precision IS NULL
       	 OR @Precision = ''
         OR @Precision < 1
         OR @Precision > 38
        ) 
        BEGIN
            SET @Msg = '@Precision for this @DataTypeText must be > 0 and <= 38.'
            RAISERROR(@Msg, 16, 1)
        END
        IF (@Scale IS NULL
        -- The next statement MUST be commented. For integer data types, a '0' is the same
        -- as '', but 0 is a valid parameter for this pattern. Under the covers, SQL Server
        -- will treat the '' as a 0, so this is OK for us. This is SQL Server specific behavior.
      	 --OR @Scale = ''
        OR @Scale > @Precision
      	) 
        BEGIN
            SET @Msg = '@Scale for this @DataTypeText must be <= @Precision.'
            RAISERROR(@Msg, 16, 1)
        END
        ELSE
        BEGIN
   	        SELECT @FormattedColumnData = @DataTypeText  + '(' + CAST(@Precision AS VARCHAR(2)) + ',' + CAST(@Scale AS VARCHAR(2)) + ')'
        END
    END	

    ELSE
    IF (@DataTypeText IN ('IMAGE','NTEXT','TEXT','TIMESTAMP'))
    BEGIN
	    SET @Msg = 'Data type: ' +  @DataTypeText + ' is not supported by MD3.CreateColumn because it is deprecated in SQL Server 2008.'
	    RAISERROR(@Msg, 16, 1) 	
    END
    
    ELSE
    IF (@DataTypeText <> 'BIGINT'
    AND @DataTypeText <> 'BIT'
    AND @DataTypeText <> 'DATE'
    AND @DataTypeText <> 'DATETIME'
    AND @DataTypeText <> 'DATETIME2'
    AND @DataTypeText <> 'DATETIMEOFFSET'
    AND @DataTypeText <> 'FLOAT'
    AND @DataTypeText <> 'INT'
    AND @DataTypeText <> 'MONEY'
    AND @DataTypeText <> 'REAL'
    AND @DataTypeText <> 'SMALLDATETIME'
    AND @DataTypeText <> 'SMALLINT'
    AND @DataTypeText <> 'SMALLMONEY'
    AND @DataTypeText <> 'SQL_VARIANT'
    AND @DataTypeText <> 'SYSNAME'
    AND @DataTypeText <> 'TIME'
    AND @DataTypeText <> 'TINYINT'
    AND @DataTypeText <> 'UNIQUEIDENTIFIER'
    AND @DataTypeText <> 'XML'
    )
    BEGIN
	    SET @Msg = 'Data type: ' +  @DataTypeText + ' is not supported by MD3.CreateColumn.'
	    RAISERROR(@Msg, 16, 1) 	
    END

	SELECT 'Stubbed.'


END

GO
